/*
 *  Copyright 2011 Arngrimur Bjarnason 
 *  
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
 
 

/**
 * 
 */
package org.arngrimur.deviationtable.db;

import java.util.Arrays;
import java.util.Collection;

import org.arngrimur.deviationtable.compass.Compass;
import org.arngrimur.deviationtable.degree.CompassDegree;
import org.arngrimur.deviationtable.degree.ReferenceDegree;


import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

/**
 * @author Arngrimur Bjarnason, arngrimurbjarnason@gmail.com
 * 
 */

public class DatabaseConnector {
	final static String REF_TABLE = "reference";
	final static String ID = "id";
	final static String DEGREE = "degree";
	final static String REF_ID = "ref_id";
	private final static String CREATE_TABLE = "CREATE TABLE " + REF_TABLE
			+ " ( " + ID + " INTEGER PRIMARY KEY," + DEGREE
			+ " INTEGER UNIQUE ON CONFLICT IGNORE)";
	private final static String tag = "SqLiteHelper";
	private static final int DATABASE_VERSION = 1;
	private Context context;
	private SQLiteDatabase db;
	private Throwable error;

	public DatabaseConnector(Context context) {
		this.context = context;
		SqLiteHelper helper = new SqLiteHelper(context);
		this.db = helper.getWritableDatabase();
	}

	/**
	 * Reads all values in the database.
	 * 
	 * @param referenceValues
	 *            The saved reference values
	 * @param compasses
	 *            The saved compass values
	 */
	public void readMappedValues(Collection<ReferenceDegree> referenceValues,
			Collection<Compass> compasses, DataModel model) {
		SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
		String tables = builder.getTables();

		Cursor cursor = db.query(REF_TABLE, new String[] { DEGREE }, null,
				null, null, null, null);
		while (cursor.moveToNext()) {
			referenceValues.add(new ReferenceDegree(cursor.getDouble(0), model.getReferenceCompass()));
		}
		ReferenceDegree[] references = new ReferenceDegree[referenceValues.size()];
		references = referenceValues.toArray(references);

		// sql statement to execute
		// select name from sqlite_master where type = 'table'
		cursor = this.executeQuery("sqlite_master", new String[] { "name" },
				"type=\'table\'", null, null, null, null);
		while (cursor.moveToNext()) {
			String table = cursor.getString(0);
			if (!table.equals(DatabaseConnector.REF_TABLE)
					&& !table.equals("android_metadata")) {
				Cursor values = db.rawQuery(
						"SELECT r.degree as ref , t.degree as deg FROM reference as r, "
								+ table + " as t WHERE r.id = t.ref_id", null);
				Compass compass = new Compass(table);
				while (values.moveToNext()) {
					int index = Arrays.binarySearch(references, new ReferenceDegree(
							values.getDouble(0), model.getReferenceCompass()));
					if (index >= 0) {
						compass.setReadValue(references[index], new CompassDegree(
								values.getDouble(1), compass, references[index]));
					}
				}
				compasses.add(compass);
			}
		}
	}

	/** Gets the database. */
	public SQLiteDatabase getDb() {
		return this.db;
	}

	/**
	 * Executes a sql statement
	 * 
	 * @param statement
	 *            The statement to execute
	 * @return true if the statement succeeded otherwise false. If the statement
	 *         failed the reason can be checked through {@link #getError()}.
	 */
	public boolean executeSql(String statement) {
		boolean success = true;
		try {
			this.db.execSQL(statement);
		} catch (SQLException e) {
			success = false;
			this.error = e.getCause();
		}
		return success;
	}

	/**
	 * Executes a query on the data base.
	 * 
	 * @see SQLiteDatabase#query(String, String[], String, String[], String,
	 *      String, String)
	 */
	public Cursor executeQuery(String table, String[] columns,
			String selection, String[] selectionArgs, String groupBy,
			String having, String orderBy) {

		return this.db.query(table, columns, selection, selectionArgs, groupBy,
				having, orderBy);
	}

	/**
	 * Gets the last sql error if {@link #executeSql(String)} returned false.
	 * The error is set to <code>null</code> after exiting the function.
	 * 
	 * @return the error or null in no error hass occures
	 */
	public Throwable getError() {
		Throwable ret = new Throwable(this.error);
		error = null;
		return ret;
	}

	private static class SqLiteHelper extends SQLiteOpenHelper {
		/**
		 * Constructor
		 */
		public SqLiteHelper(Context context) {
			// calls the super constructor, requesting the default cursor
			// factory.
			super(context, REF_TABLE, null, DATABASE_VERSION);
		}

		/**
		 * 
		 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database
		 *      .sqlite.SQLiteDatabase)
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE);
		}

		/**
		 * 
		 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database
		 *      .sqlite.SQLiteDatabase, int, int)
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + REF_TABLE);
			Log.d(tag, "Updating from db version " + oldVersion + " to "
					+ newVersion);
			this.onCreate(db);
		}

	}
}